﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using hsgsSHXX.DBUtility;//Please add references
using System.Collections.Generic;
namespace hsgsSHXX.DAL
{
	/// <summary>
	/// 数据访问类:xsf
	/// </summary>
	public partial class xsf
	{
		public xsf()
		{}
		#region  Method



		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(hsgsSHXX.Model.xsf model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into xsf(");
			strSql.Append("id,xm,depart_id,user_kind,sfzh,xb,birthday,file_using,using_cardnum,id_kind,h_children_number,h_address,h_company,h_telephonenumber,h_rank,h_arrive_time,h_left_time)");
			strSql.Append(" values (");
			strSql.Append("@id,@xm,@depart_id,@user_kind,@sfzh,@xb,@birthday,@file_using,@using_cardnum,@id_kind,@h_children_number,@h_address,@h_company,@h_telephonenumber,@h_rank,@h_arrive_time,@h_left_time)");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.NVarChar,255),
					new SqlParameter("@xm", SqlDbType.NVarChar,255),
					new SqlParameter("@depart_id", SqlDbType.NVarChar,255),
					new SqlParameter("@user_kind", SqlDbType.NVarChar,255),
					new SqlParameter("@sfzh", SqlDbType.NVarChar,255),
					new SqlParameter("@xb", SqlDbType.NVarChar,255),
					new SqlParameter("@birthday", SqlDbType.NVarChar,255),
					new SqlParameter("@file_using", SqlDbType.NVarChar,255),
					new SqlParameter("@using_cardnum", SqlDbType.NVarChar,255),
					new SqlParameter("@id_kind", SqlDbType.NVarChar,255),
					new SqlParameter("@h_children_number", SqlDbType.NVarChar,255),
					new SqlParameter("@h_address", SqlDbType.NVarChar,255),
					new SqlParameter("@h_company", SqlDbType.NVarChar,255),
					new SqlParameter("@h_telephonenumber", SqlDbType.NVarChar,255),
					new SqlParameter("@h_rank", SqlDbType.NVarChar,255),
					new SqlParameter("@h_arrive_time", SqlDbType.NVarChar,255),
					new SqlParameter("@h_left_time", SqlDbType.NVarChar,255)};
			parameters[0].Value = model.id;
			parameters[1].Value = model.xm;
			parameters[2].Value = model.depart_id;
			parameters[3].Value = model.user_kind;
			parameters[4].Value = model.sfzh;
			parameters[5].Value = model.xb;
			parameters[6].Value = model.birthday;
			parameters[7].Value = model.file_using;
			parameters[8].Value = model.using_cardnum;
			parameters[9].Value = model.id_kind;
			parameters[10].Value = model.h_children_number;
			parameters[11].Value = model.h_address;
			parameters[12].Value = model.h_company;
			parameters[13].Value = model.h_telephonenumber;
			parameters[14].Value = model.h_rank;
			parameters[15].Value = model.h_arrive_time;
			parameters[16].Value = model.h_left_time;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(hsgsSHXX.Model.xsf model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update xsf set ");
			strSql.Append("id=@id,");
			strSql.Append("xm=@xm,");
			strSql.Append("depart_id=@depart_id,");
			strSql.Append("user_kind=@user_kind,");
			strSql.Append("sfzh=@sfzh,");
			strSql.Append("xb=@xb,");
			strSql.Append("birthday=@birthday,");
			strSql.Append("file_using=@file_using,");
			strSql.Append("using_cardnum=@using_cardnum,");
			strSql.Append("id_kind=@id_kind,");
			strSql.Append("h_children_number=@h_children_number,");
			strSql.Append("h_address=@h_address,");
			strSql.Append("h_company=@h_company,");
			strSql.Append("h_telephonenumber=@h_telephonenumber,");
			strSql.Append("h_rank=@h_rank,");
			strSql.Append("h_arrive_time=@h_arrive_time,");
			strSql.Append("h_left_time=@h_left_time");
			strSql.Append(" where ");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.NVarChar,255),
					new SqlParameter("@xm", SqlDbType.NVarChar,255),
					new SqlParameter("@depart_id", SqlDbType.NVarChar,255),
					new SqlParameter("@user_kind", SqlDbType.NVarChar,255),
					new SqlParameter("@sfzh", SqlDbType.NVarChar,255),
					new SqlParameter("@xb", SqlDbType.NVarChar,255),
					new SqlParameter("@birthday", SqlDbType.NVarChar,255),
					new SqlParameter("@file_using", SqlDbType.NVarChar,255),
					new SqlParameter("@using_cardnum", SqlDbType.NVarChar,255),
					new SqlParameter("@id_kind", SqlDbType.NVarChar,255),
					new SqlParameter("@h_children_number", SqlDbType.NVarChar,255),
					new SqlParameter("@h_address", SqlDbType.NVarChar,255),
					new SqlParameter("@h_company", SqlDbType.NVarChar,255),
					new SqlParameter("@h_telephonenumber", SqlDbType.NVarChar,255),
					new SqlParameter("@h_rank", SqlDbType.NVarChar,255),
					new SqlParameter("@h_arrive_time", SqlDbType.NVarChar,255),
					new SqlParameter("@h_left_time", SqlDbType.NVarChar,255)};
			parameters[0].Value = model.id;
			parameters[1].Value = model.xm;
			parameters[2].Value = model.depart_id;
			parameters[3].Value = model.user_kind;
			parameters[4].Value = model.sfzh;
			parameters[5].Value = model.xb;
			parameters[6].Value = model.birthday;
			parameters[7].Value = model.file_using;
			parameters[8].Value = model.using_cardnum;
			parameters[9].Value = model.id_kind;
			parameters[10].Value = model.h_children_number;
			parameters[11].Value = model.h_address;
			parameters[12].Value = model.h_company;
			parameters[13].Value = model.h_telephonenumber;
			parameters[14].Value = model.h_rank;
			parameters[15].Value = model.h_arrive_time;
			parameters[16].Value = model.h_left_time;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete()
		{
			//该表无主键信息，请自定义主键/条件字段
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from xsf ");
			strSql.Append(" where ");
			SqlParameter[] parameters = {
};

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public hsgsSHXX.Model.xsf GetModel()
		{
			//该表无主键信息，请自定义主键/条件字段
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 id,xm,depart_id,user_kind,sfzh,xb,birthday,file_using,using_cardnum,id_kind,h_children_number,h_address,h_company,h_telephonenumber,h_rank,h_arrive_time,h_left_time from xsf ");
			strSql.Append(" where ");
			SqlParameter[] parameters = {
};

			hsgsSHXX.Model.xsf model=new hsgsSHXX.Model.xsf();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["id"]!=null && ds.Tables[0].Rows[0]["id"].ToString()!="")
				{
					model.id=ds.Tables[0].Rows[0]["id"].ToString();
				}
				if(ds.Tables[0].Rows[0]["xm"]!=null && ds.Tables[0].Rows[0]["xm"].ToString()!="")
				{
					model.xm=ds.Tables[0].Rows[0]["xm"].ToString();
				}
				if(ds.Tables[0].Rows[0]["depart_id"]!=null && ds.Tables[0].Rows[0]["depart_id"].ToString()!="")
				{
					model.depart_id=ds.Tables[0].Rows[0]["depart_id"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_kind"]!=null && ds.Tables[0].Rows[0]["user_kind"].ToString()!="")
				{
					model.user_kind=ds.Tables[0].Rows[0]["user_kind"].ToString();
				}
				if(ds.Tables[0].Rows[0]["sfzh"]!=null && ds.Tables[0].Rows[0]["sfzh"].ToString()!="")
				{
					model.sfzh=ds.Tables[0].Rows[0]["sfzh"].ToString();
				}
				if(ds.Tables[0].Rows[0]["xb"]!=null && ds.Tables[0].Rows[0]["xb"].ToString()!="")
				{
					model.xb=ds.Tables[0].Rows[0]["xb"].ToString();
				}
				if(ds.Tables[0].Rows[0]["birthday"]!=null && ds.Tables[0].Rows[0]["birthday"].ToString()!="")
				{
					model.birthday=ds.Tables[0].Rows[0]["birthday"].ToString();
				}
				if(ds.Tables[0].Rows[0]["file_using"]!=null && ds.Tables[0].Rows[0]["file_using"].ToString()!="")
				{
					model.file_using=ds.Tables[0].Rows[0]["file_using"].ToString();
				}
				if(ds.Tables[0].Rows[0]["using_cardnum"]!=null && ds.Tables[0].Rows[0]["using_cardnum"].ToString()!="")
				{
					model.using_cardnum=ds.Tables[0].Rows[0]["using_cardnum"].ToString();
				}
				if(ds.Tables[0].Rows[0]["id_kind"]!=null && ds.Tables[0].Rows[0]["id_kind"].ToString()!="")
				{
					model.id_kind=ds.Tables[0].Rows[0]["id_kind"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_children_number"]!=null && ds.Tables[0].Rows[0]["h_children_number"].ToString()!="")
				{
					model.h_children_number=ds.Tables[0].Rows[0]["h_children_number"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_address"]!=null && ds.Tables[0].Rows[0]["h_address"].ToString()!="")
				{
					model.h_address=ds.Tables[0].Rows[0]["h_address"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_company"]!=null && ds.Tables[0].Rows[0]["h_company"].ToString()!="")
				{
					model.h_company=ds.Tables[0].Rows[0]["h_company"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_telephonenumber"]!=null && ds.Tables[0].Rows[0]["h_telephonenumber"].ToString()!="")
				{
					model.h_telephonenumber=ds.Tables[0].Rows[0]["h_telephonenumber"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_rank"]!=null && ds.Tables[0].Rows[0]["h_rank"].ToString()!="")
				{
					model.h_rank=ds.Tables[0].Rows[0]["h_rank"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_arrive_time"]!=null && ds.Tables[0].Rows[0]["h_arrive_time"].ToString()!="")
				{
					model.h_arrive_time=ds.Tables[0].Rows[0]["h_arrive_time"].ToString();
				}
				if(ds.Tables[0].Rows[0]["h_left_time"]!=null && ds.Tables[0].Rows[0]["h_left_time"].ToString()!="")
				{
					model.h_left_time=ds.Tables[0].Rows[0]["h_left_time"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,xm,depart_id,user_kind,sfzh,xb,birthday,file_using,using_cardnum,id_kind,h_children_number,h_address,h_company,h_telephonenumber,h_rank,h_arrive_time,h_left_time ");
			strSql.Append(" FROM xsf ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" id,xm,depart_id,user_kind,sfzh,xb,birthday,file_using,using_cardnum,id_kind,h_children_number,h_address,h_company,h_telephonenumber,h_rank,h_arrive_time,h_left_time ");
			strSql.Append(" FROM xsf ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM xsf ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T. desc");
			}
			strSql.Append(")AS Row, T.*  from xsf T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "xsf";
			parameters[1].Value = "";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
        public List<hsgsSHXX.Model.xsf> DataTableToList(DataTable dt)
        {
            List<hsgsSHXX.Model.xsf> modelList = new List<hsgsSHXX.Model.xsf>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                hsgsSHXX.Model.xsf model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new hsgsSHXX.Model.xsf();
                    if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
                    {
                        model.id = dt.Rows[n]["id"].ToString();
                    }
                    if (dt.Rows[n]["xm"] != null && dt.Rows[n]["xm"].ToString() != "")
                    {
                        model.xm = dt.Rows[n]["xm"].ToString();
                    }
                    if (dt.Rows[n]["depart_id"] != null && dt.Rows[n]["depart_id"].ToString() != "")
                    {
                        model.depart_id = dt.Rows[n]["depart_id"].ToString();
                    }
                    if (dt.Rows[n]["user_kind"] != null && dt.Rows[n]["user_kind"].ToString() != "")
                    {
                        model.user_kind = dt.Rows[n]["user_kind"].ToString();
                    }
                    if (dt.Rows[n]["sfzh"] != null && dt.Rows[n]["sfzh"].ToString() != "")
                    {
                        model.sfzh = dt.Rows[n]["sfzh"].ToString();
                    }
                    if (dt.Rows[n]["xb"] != null && dt.Rows[n]["xb"].ToString() != "")
                    {
                        model.xb = dt.Rows[n]["xb"].ToString();
                    }
                    if (dt.Rows[n]["birthday"] != null && dt.Rows[n]["birthday"].ToString() != "")
                    {
                        model.birthday = dt.Rows[n]["birthday"].ToString();
                    }
                    if (dt.Rows[n]["file_using"] != null && dt.Rows[n]["file_using"].ToString() != "")
                    {
                        model.file_using = dt.Rows[n]["file_using"].ToString();
                    }
                    if (dt.Rows[n]["using_cardnum"] != null && dt.Rows[n]["using_cardnum"].ToString() != "")
                    {
                        model.using_cardnum = dt.Rows[n]["using_cardnum"].ToString();
                    }
                    if (dt.Rows[n]["id_kind"] != null && dt.Rows[n]["id_kind"].ToString() != "")
                    {
                        model.id_kind = dt.Rows[n]["id_kind"].ToString();
                    }
                    if (dt.Rows[n]["h_children_number"] != null && dt.Rows[n]["h_children_number"].ToString() != "")
                    {
                        model.h_children_number = dt.Rows[n]["h_children_number"].ToString();
                    }
                    if (dt.Rows[n]["h_address"] != null && dt.Rows[n]["h_address"].ToString() != "")
                    {
                        model.h_address = dt.Rows[n]["h_address"].ToString();
                    }
                    if (dt.Rows[n]["h_company"] != null && dt.Rows[n]["h_company"].ToString() != "")
                    {
                        model.h_company = dt.Rows[n]["h_company"].ToString();
                    }
                    if (dt.Rows[n]["h_telephonenumber"] != null && dt.Rows[n]["h_telephonenumber"].ToString() != "")
                    {
                        model.h_telephonenumber = dt.Rows[n]["h_telephonenumber"].ToString();
                    }
                    if (dt.Rows[n]["h_rank"] != null && dt.Rows[n]["h_rank"].ToString() != "")
                    {
                        model.h_rank = dt.Rows[n]["h_rank"].ToString();
                    }
                    if (dt.Rows[n]["h_arrive_time"] != null && dt.Rows[n]["h_arrive_time"].ToString() != "")
                    {
                        model.h_arrive_time = dt.Rows[n]["h_arrive_time"].ToString();
                    }
                    if (dt.Rows[n]["h_left_time"] != null && dt.Rows[n]["h_left_time"].ToString() != "")
                    {
                        model.h_left_time = dt.Rows[n]["h_left_time"].ToString();
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        public List<Model.xsf> GetFondListByPage(int pageSize, int pageIndex, string strWhere, string fOrderFiled, out int total)
        {
            if (fOrderFiled == "")
            {
                fOrderFiled = "id desc";
            }
            SqlParameter[] parameters = {  
                    new SqlParameter("@tableName", SqlDbType.VarChar, 50),  
                    new SqlParameter("@fields", SqlDbType.VarChar, 2000),  
                    new SqlParameter("@orderField", SqlDbType.VarChar, 500),  
                    new SqlParameter("@sqlWhere", SqlDbType.VarChar,2000),  
                    new SqlParameter("@pageSize", SqlDbType.Int),  
                    new SqlParameter("@pageIndex", SqlDbType.Int),  
                    new SqlParameter("@TotalPage", SqlDbType.Int),
                    new SqlParameter("@TotalRecordCount", SqlDbType.Int) 
                    };

            parameters[0].Value = "xsf";
            parameters[1].Value = "*";
            parameters[2].Value = fOrderFiled;
            parameters[3].Value = strWhere;
            parameters[4].Value = pageSize;
            parameters[5].Value = pageIndex;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.Output;
            DataSet ds = DbHelperSQL.RunProcedure("up_GetRecordByPage", parameters, "ds", 60 * 30);
            total = int.Parse(parameters[7].Value.ToString());
            return DataTableToList(ds.Tables[0]);

        }
	}
}

